package com.FH.applyForm.officeServies.export.notice;

import com.FH.util.AccessDB;
import com.FH.util.ExportExcel;
import com.FH.util.ExportPDF;
import com.FH.util.ResultDTO;
import com.itextpdf.text.DocumentException;
import org.codehaus.jettison.json.JSONException;
import org.codehaus.jettison.json.JSONObject;

import javax.ejb.Stateless;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

@Stateless
public class ExportNoticeDAOImpl extends AccessDB implements ExportNoticeDAO {

    //根据条件导出公告
    @Override
    public ResultDTO exportNotice(String condtion) {

        ResultDTO resultDTO = new ResultDTO();
        String downloadUrl = "";
        String pdfDownLoadUrl = "";

        SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd-HH:mm:ss");

        List<String[]> seresult = new ArrayList<String[]>();
        List<ExportNoticeShowDTO> result = new ArrayList<ExportNoticeShowDTO>();

        try {
            JSONObject search = new JSONObject(condtion);

            String sql = "SELECT n.notice_title,n.notice_types,n.notice_html,n.notice_main,at.attachment_addr,nt.noticetype_name,km2.member_name,o.organization_name," +
                    "n.notice_starttime,n.notice_endtime,km.member_name,n.notice_createtime from notice n " +
                    "left join notice_member nm " +
                    "ON nm.notice_id = n.notice_id " +
                    "left join organization o " +
                    "ON o.organization_id = n.rangeorganization_id " +
                    "left JOIN notice_type nt " +
                    "ON nt.id = n.notice_type_id " +
                    "LEFT JOIN kam_member km " +
                    "on km.member_id = n.member_id " +
                    "LEFT JOIN attachment at " +
                    "on at.attachment_id = n.attachment_id " +
                    "left JOIN kam_member km2 " +
                    "on km2.member_id = nm.member_id " +
                    "where 1=1";
            if (search.has("starttime")) {
                if(null != search.get("starttime").toString() && !search.get("starttime").toString().isEmpty()){
                    sql += " and n.notice_createtime>'" + search.get("starttime").toString() + "' ";
                }
            }
            if (search.has("endtime")) {
                if(null != search.get("endtime").toString() && !search.get("endtime").toString().isEmpty()){
                    sql += " and n.notice_createtime< '" + search.get("endtime").toString() + "' ";
                }
            }
            if (search.has("member_id")) {
                if(null != search.get("member_id").toString() && !search.get("member_id").toString().isEmpty()){
                    sql += " and km.member_id = '" + search.get("member_id").toString() + "' ";
                }
            }
            if (search.has("organization_id")) {
                if(null != search.get("organization_id").toString() && !search.get("organization_id").toString().isEmpty()){
                    sql += " and n.rangeorganization_id ='" + search.get("organization_id").toString() + "' ";
                }
            }

            sql += " GROUP BY n.notice_id";

            seresult = this.getReportingTo(sql);
            for (int i = 0; i < seresult.size(); i++){
                ExportNoticeShowDTO exportNoticeShowDTO = new ExportNoticeShowDTO();
                exportNoticeShowDTO.setNotice_title(seresult.get(i)[1] == null ? "" :seresult.get(i)[1] );
                if(seresult.get(i)[2].equals("1")){
                    exportNoticeShowDTO.setNotice_types("按部门发布");
                }
                if(seresult.get(i)[2].equals("3")){
                    exportNoticeShowDTO.setNotice_types("按人员发布");
                }
                exportNoticeShowDTO.setNotice_html(seresult.get(i)[3] == null ? "" :seresult.get(i)[3] );
                exportNoticeShowDTO.setNotice_main(seresult.get(i)[4] == null ? "" :seresult.get(i)[4] );
                exportNoticeShowDTO.setAttachment_addr(seresult.get(i)[5] == null ? "" :seresult.get(i)[5]);
                exportNoticeShowDTO.setNoticetype_name(seresult.get(i)[6] == null ? "" :seresult.get(i)[6]);
                exportNoticeShowDTO.setMember_name(seresult.get(i)[7] == null ? "" :seresult.get(i)[7]);
                exportNoticeShowDTO.setOrganization_name(seresult.get(i)[8] == null ? "" :seresult.get(i)[8]);
                exportNoticeShowDTO.setNotice_starttime(seresult.get(i)[9] == null ? "" :seresult.get(i)[9]);
                exportNoticeShowDTO.setNotice_endtime(seresult.get(i)[10] == null ? "" :seresult.get(i)[10]);
                exportNoticeShowDTO.setMembername(seresult.get(i)[11] == null ? "" :seresult.get(i)[11]);
                exportNoticeShowDTO.setNotice_createtime(seresult.get(i)[12] == null ? "" :seresult.get(i)[12]);

                result.add(exportNoticeShowDTO);
            }

            String[] headers = {"公告主题", "公告类型", "公告内容", "公告总结", "附件下载地址", "公告类型名称", "公告接收人", "公告部门", "公告开始时间", "公告结束时间","公告发布人姓名","公告创建时间"};
            String title = "公告统计";
            String filename = "公告统计表";//+ df.format(new Date())

            ExportExcel<ExportNoticeShowDTO> exportExcel = new ExportExcel<ExportNoticeShowDTO>();
            downloadUrl = exportExcel.createExcelFile(exportExcel, title, headers, result, filename);

        try {
            pdfDownLoadUrl = ExportPDF.createPDFFile(seresult, title, headers, filename);
        } catch (DocumentException e) {
            e.printStackTrace();
        }
    } catch (JSONException e) {
        e.printStackTrace();
    }

    if(null != downloadUrl && "" != downloadUrl){
        resultDTO.setStatus("90000");
        resultDTO.setDescritpion(downloadUrl);
        resultDTO.setStr(pdfDownLoadUrl);
        resultDTO.setValues(result);
    }
    else{
        resultDTO.setStatus("90001");
    }

        return resultDTO;
    }
}
